package org.scct.chenhui.dao;

import org.scct.chenhui.model.UserBean;
import org.scct.chenhui.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 封装了对user表操作的方法
 */
public class UserDao {

    public UserBean Login(String username, String password, int role) {
        UserBean user = null;
//      1.  参数化查询语句
        String sql = "SELECT * from  bm_user where username=? and password=? and role=?";
//      2.   创建连接
        Connection connection = DBUtils.getConnection();
        try {
            //3 创建预编译查询对象
            PreparedStatement pstmt = connection.prepareStatement(sql);
            //4 添加参数
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            pstmt.setInt(3, role);
//            5 查询 获取结果集
            ResultSet rs = pstmt.executeQuery();
            //6rs.next()移动游标，返回true表示移动成功，下一条有内容，返回false表示当前条是最后一条，没后续的记录
            if (rs.next()) {
                int id = rs.getInt("id");
                String sex = rs.getString("sex");
                String phone = rs.getString("phone");
                user = new UserBean(id, username, password, role, sex, phone);
            }
//            关闭数据库连接
            pstmt.close();
            DBUtils.closeConnection(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return user;
    }

//    用户添加操作

    /**
     * user对象，包含 用户名，密码，性别，手机，角色属性，待添加的内容
     *
     * @return true添加成功  false添加失败
     */
    public boolean AddUser(UserBean user) {
        int result = 0;
        //1、构建参数化查询语句(根据navicat中的sql语句）
        String sql = "insert into bm_user VALUES(null,?,?,?,?,?)";
        //2.连接对象
        Connection connection = DBUtils.getConnection();
//        预编译查询对象
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setInt(3, user.getRole());
            pstmt.setString(4, user.getSex());
            pstmt.setString(5, user.getPhone());
            //执行sql语句
            result = pstmt.executeUpdate();
            pstmt.close();
            DBUtils.closeConnection(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result == 1;
    }

    //    获取所有的用户信息
    public List<UserBean> getAllUsers() {
        String sql = "select * from bm_user";
        List<UserBean> users = new ArrayList<>();
        Connection connection = DBUtils.getConnection();
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
//           便利结果集获取每行记录表示的数据对象
            while (rs.next()) {
//               从当前行获取所有的字段值
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                int role = rs.getInt("role");
                String sex = rs.getString("sex");
                String phone = rs.getString("phone");
//               根据字段值创建用户对象
                UserBean bean = new UserBean(id, username, password, role, sex, phone);
//               讲用户对象加入集合总
                users.add(bean);
            }
            pstmt.close();
            DBUtils.closeConnection(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return users;
    }

     public  UserBean getUserById(int id)
     {
         String sql = "select * from bm_user where id=?";
         UserBean user =null;
         Connection connection = DBUtils.getConnection();
         try {
             PreparedStatement pstmt = connection.prepareStatement(sql);
             pstmt.setInt(1,id);
             ResultSet rs = pstmt.executeQuery();
//           便利结果集获取每行记录表示的数据对象
             if (rs.next()) {
//               从当前行获取所有的字段值

                 String username = rs.getString("username");
                 String password = rs.getString("password");
                 int role = rs.getInt("role");
                 String sex = rs.getString("sex");
                 String phone = rs.getString("phone");
//               根据字段值创建用户对象
                   user = new UserBean(id, username, password, role, sex, phone);
             }
             pstmt.close();
             DBUtils.closeConnection(connection);
         } catch (SQLException e) {
             throw new RuntimeException(e);
         }
         return user;
     }

    public boolean isDoubleUserName(String name) {
        UserBean user = null;
//      1.  参数化查询语句
        String sql = "SELECT * from  bm_user where username=?";
//      2.   创建连接
        Connection connection = DBUtils.getConnection();
        try {
            //3 创建预编译查询对象
            PreparedStatement pstmt = connection.prepareStatement(sql);
            //4 添加参数
            pstmt.setString(1, name);
//            5 查询 获取结果集
            ResultSet rs = pstmt.executeQuery();
            //6rs.next()移动游标，返回true表示移动成功，下一条有内容，返回false表示当前条是最后一条，没后续的记录
            if (rs.next()) {
                return true;
            }
//            关闭数据库连接
            pstmt.close();
            DBUtils.closeConnection(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }

    public boolean isDoublePhone(String phone) {
        UserBean user = null;
//      1.  参数化查询语句
        String sql = "SELECT * from  bm_user where phone=?";
//      2.   创建连接
        Connection connection = DBUtils.getConnection();
        try {
            //3 创建预编译查询对象
            PreparedStatement pstmt = connection.prepareStatement(sql);
            //4 添加参数
            pstmt.setString(1, phone);
//            5 查询 获取结果集
            ResultSet rs = pstmt.executeQuery();
            //6rs.next()移动游标，返回true表示移动成功，下一条有内容，返回false表示当前条是最后一条，没后续的记录
            if (rs.next()) {
                return true;
            }
//            关闭数据库连接
            pstmt.close();
            DBUtils.closeConnection(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }

    public boolean updateUser(UserBean user) {
        int res;
        String sql = "UPDATE bm_user SET password=? ,sex=? ,phone=? WHERE id=?";
        Connection connection = DBUtils.getConnection();
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, user.getPassword());
            pstmt.setString(2, user.getSex());
            pstmt.setString(3, user.getPhone());
            pstmt.setInt(4, user.getId());

            res = pstmt.executeUpdate();
            pstmt.close();
            DBUtils.closeConnection(connection);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return res == 1;
    }

}
